package com.excel.read;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.db.ColumnInfoBean;
import com.db.ForeignKey;

/**
 * 数据库字段信息，包括表，外键，主键，约束等等
 * 
 * @author Administrator
 * 
 */
public class DBTable extends GridTable {
	int typeIndex = -1;
	int nullIndex = -1;
	int defaultIndex = -1;
	// 外键
	int fkIndex = -1;

	/**
	 * 数据库字段
	 */
	int codeIndex = -1;
	int pkIndex = -1;

	/**
	 * 显示名
	 */
	int displayNameIndex = -1;

	/**
	 * 关联表 配置初始化过程中，外键关联将通过fk信息初始化
	 */
	public List<ForeignKey> fkList = new ArrayList<ForeignKey>();
    /**
     * 正向关联
     */
	Map<String, ForeignKey> fkMap = new HashMap<String, ForeignKey>();

	/**
	 * 反向关联,为了建立一棵完整的查询树,需要反向关联提高查询速度
	 */
	Map<String, ForeignKey> fkReverseMap = new HashMap<String, ForeignKey>();
	
	/**
	 * 列名
	 */
	public String fieldNames[];
	
	/**
	 * 业务主键
	 */
	//TODO
	public List<ColumnInfoBean> realPK = new ArrayList<ColumnInfoBean>();
	
	/**
	 * 唯一字段
	 */
	public Map<String, ColumnInfoBean> uniqueMap = new HashMap<String, ColumnInfoBean>();
	
	
	
	/**
	 * 数据库列相关元数据
	 */
	public Map <String, ColumnInfoBean>fieldMap = new HashMap<String, ColumnInfoBean>();
	
	
	public ColumnInfoBean pk;
	public List<ColumnInfoBean> pks = new ArrayList<ColumnInfoBean>();
	/**
	 * 
	 * @param fieldName
	 * @return
	 */
	
	public String getType(String fieldName) {
		int rowIndex = -1;
		for (int i = 0; i < this.data.length; i++) {
			String value = data[i][0];
			if (value.equalsIgnoreCase(fieldName)) {
				rowIndex = i;
				break;
			}
		}
		return this.data[rowIndex][typeIndex];
	}

	public List getNotNullFields() {
		List aList = new ArrayList();
		for (int i = 1; i < this.data.length; i++) {
			String value = this.data[i][nullIndex];
			if (value == null || value.equals("")) {

			} else {
				aList.add(this.data[i][0]);
			}
		}
		return aList;
	}
	
	public synchronized void setColumnInfo(String fieldName) {
		ColumnInfoBean bean = null;		
		bean = new ColumnInfoBean();		
		String[][] vs = this.getData();	
		HashMap<String, String> hm = new HashMap();			
		for (int i = 1; i < vs.length; i++) {
			if(fieldName.equalsIgnoreCase(vs[i][0])) {
			for (int j = 0; j < vs[0].length; j++) {				
				String aName = vs[0][j];
				String aValue = vs[i][j];
				hm.put(aName, aValue);
				}
			}
		}					
		bean.setDisplayName((String) hm.get("名称"));
		bean.setName((String) hm.get("代码"));
		String allowNullStr = (String) hm.get("允许为空");
		if (allowNullStr == null || allowNullStr.equalsIgnoreCase("Y")
				|| allowNullStr.equalsIgnoreCase("")) {
			bean.setAllowNull(true);
		} else {
			bean.setAllowNull(false);
		}
		// 类型
		bean.setTypeName((String) hm.get("数据类型"));
		bean.setDefaultValue((String) hm.get("缺省值"));
		
		//去括号
		int endIndex = bean.getTypeName().indexOf("(")==-1?bean.getTypeName().length():bean.getTypeName().indexOf("(");
		String noPresitionTypeName = bean.getTypeName().substring(0, endIndex);
		bean.setNoPresitionTypeName(noPresitionTypeName.toUpperCase());
		this.fieldMap.put(fieldName, bean);	
	}

	
	
	public synchronized ColumnInfoBean getColumnInfo(String fieldName) {
		ColumnInfoBean bean = this.fieldMap.get(fieldName);
		return bean;
	}

	public List<ColumnInfoBean> getColumnInfos() {
		List<ColumnInfoBean> list = new ArrayList<ColumnInfoBean>();
		Iterator itr = fieldMap.values().iterator();
		for(String name: fieldNames) {
			ColumnInfoBean aColumnInfoBean = fieldMap.get(name);
			list.add(aColumnInfoBean);
		}		
		return list;
	}
	
	
	public List<ColumnInfoBean> getNoPKColumnInfos() {
		List<ColumnInfoBean> list = new ArrayList<ColumnInfoBean>();
		for(String fieldName : getNoPKNameList()) {
			ColumnInfoBean aColumnInfoBean = fieldMap.get(fieldName);
			list.add(aColumnInfoBean);
		}		
		return list;
	}
	
	public List<ColumnInfoBean> getPKColumnInfos() {
		List<ColumnInfoBean> list = new ArrayList<ColumnInfoBean>();
		for(String fieldName : getPKNameList()) {
			ColumnInfoBean aColumnInfoBean = fieldMap.get(fieldName);
			list.add(aColumnInfoBean);
		}		
		return list;
	}
	
	public void setColumnInfos() {
		String[][] vs = this.getData();	
		fieldNames = new String[vs.length-1];	
		for (int i = 1; i < vs.length; i++) {
			fieldNames[i-1] = vs[i][0];			
		}	
		
		for (int i = 0; i < fieldNames.length; i++) {			
			setColumnInfo(fieldNames[i]);
		}		
			
		
	}

	
	
	public boolean allowNull(String fieldName) {
		boolean flag = true;
		int rowIndex = -1;
		for (int i = 0; i < this.data.length; i++) {
			String value = data[i][0];
			if (value.equalsIgnoreCase(fieldName)) {
				rowIndex = i;
				break;
			}
		}
		String value = this.data[rowIndex][nullIndex];
		if (value == null || value.equals("")) {
			flag = true;
		} else {
			flag = false;
		}
		return flag;
	}

	public String getDefaultValue(String fieldName) {
		int rowIndex = -1;
		for (int i = 0; i < this.data.length; i++) {
			String value = data[i][0];
			if (value.equalsIgnoreCase(fieldName)) {
				rowIndex = i;
				break;
			}
		}
		return this.data[rowIndex][defaultIndex];
	}
	
	

	/**
	 * 取主键
	 * @param tableName
	 * @return
	 */
	public  ColumnInfoBean getPKBean(String tableName) {
		if(pk != null) {
			return pk;
		}		
//		String[][] vs = this.getData();
//		HashMap<String, String> hm = new HashMap();			
//		for (int i = 1; i < vs.length; i++) {
//			if("PK".equalsIgnoreCase(vs[i][7])) {
//			for (int j = 0; j < vs[0].length; j++) {				
//				String aName = vs[0][j];
//				String aValue = vs[i][j];
//				hm.put(aName, aValue);
//				}
//			}
//		}			
//		ColumnInfoBean bean = new ColumnInfoBean();		
//		bean.setDisplayName((String) hm.get("名称"));
//		bean.setName((String) hm.get("代码"));
//		String allowNullStr = (String) hm.get("允许为空");
//		if (allowNullStr == null || allowNullStr.equalsIgnoreCase("Y")
//				|| allowNullStr.equalsIgnoreCase("")) {
//			bean.setAllowNull(true);
//		} else {
//			bean.setAllowNull(false);
//		}
//		// 类型
//		bean.setTypeName((String) hm.get("数据类型"));
//		bean.setDefaultValue((String) hm.get("缺省值"));
//		
//		//去括号
//		String noPresitionTypeName = bean.getTypeName().substring(0, bean.getTypeName().indexOf("("));
//		bean.setNoPresitionTypeName(noPresitionTypeName.toUpperCase());
//		
//		pk = bean;
		return pk;
	}
	
	
	
	/**
	 * @param decodeName
	 *            格式：tableName(fieldName)
	 * @return
	 */
	public ForeignKey getFK(String fkFieldName, String decodeName) {
		ForeignKey foreignKey = new ForeignKey();
		int aIndex = decodeName.indexOf("(");
		String tableName = decodeName.substring(0, aIndex);
		String fieldName = decodeName.substring(aIndex+1, decodeName.trim().length()-1);
		foreignKey.setToTableName(tableName.toUpperCase());
		foreignKey.setToField(fieldName.toUpperCase());
		foreignKey.setFkTable(name.toUpperCase());
		foreignKey.setFkName(fkFieldName.toUpperCase());
		foreignKey.setFromTable(this);
		return foreignKey;

	}

	public String getFKValue(String fieldName) {
		int rowIndex = -1;
		
		for (int i = 0; i < this.data.length; i++) {
			String value = data[i][0];
			if (value.equalsIgnoreCase(fieldName)) {
				rowIndex = i;
				break;
			}
		}
		// 外键信息
		String fkField = this.data[rowIndex][fkIndex];
		if (fkField != null && !fkField.equals("")) {
			String fkFieldName = this.data[rowIndex][codeIndex];
			ForeignKey foreignKey = getFK(fkFieldName, fkField);
			fkList.add(foreignKey);			
		}
		return fkField;

	}
	
	

	public List<ForeignKey> getFkList() {
		fkList.clear();
		for (int i = 1; i < this.data.length; i++) {
			String value = data[i][fkIndex];
			String fieldName = data[i][codeIndex];
			getFKValue(fieldName);						
		}
		
		return fkList;
	}

	public boolean isFK(String fieldName) {
		boolean flag = false;
		for (int i = 0; i < this.data.length; i++) {
			String value = data[i][0];
			if (value.equalsIgnoreCase(fieldName)) {
				rowIndex = i;
				break;
			}
		}
		// 外键信息
		String fkField = this.data[rowIndex][fkIndex];
		if (fkField != null && !fkField.equals("")) {
			return true;		
		}
		return flag;
	}
	
	
	public void setFkList(List<ForeignKey> fkList) {
		this.fkList = fkList;
	}

	public void putFK(String pmbName, ForeignKey foreignKey) {
		this.fkMap.put(pmbName, foreignKey);
	}
	
	public void putReserveFK(String pmbName, ForeignKey foreignKey) {
		this.fkReverseMap.put(pmbName, foreignKey);
	}

	public ForeignKey getFK(String pmbName) {
		return this.fkMap.get(pmbName);
	}

	public ColumnInfoBean getPk() {
		return pk;
	}
	
	public ColumnInfoBean getUnique(String fieldName) {
		ColumnInfoBean aColumnInfoBean = this.uniqueMap.get(fieldName);
		return aColumnInfoBean;
	}
	
	public boolean isUnique(String fieldName) {
		ColumnInfoBean aColumnInfoBean = this.uniqueMap.get(fieldName);
		return aColumnInfoBean != null;
	}
	
	public void setUnique(String fieldName, ColumnInfoBean aColumnInfoBean) {
		this.uniqueMap.put(fieldName, aColumnInfoBean);
	}

	public void setPk(ColumnInfoBean pk) {
		this.pk = pk;
	}
	
	

	public String[] getNameList() {
		
		return this.fieldNames;
	}
	
	/**
	 * 取得非主键字段，用于更新记录
	 */
	public   List<String> getNoPKNameList() {
	    List<String> names = new ArrayList<String>();
	    String pkName = this.getPk().getName();
		for(String fieldName : this.fieldNames) {
			if(pkName.equalsIgnoreCase(fieldName)) {				
			}else {
				names.add(fieldName);
			}
		}
		return names;
	}
	
	/**
	 * 取得主键字段，用于更新记录
	 */
	public   List<String> getPKNameList() {
	    List<String> names = new ArrayList<String>();
	    String pkName = this.getPk().getName();
		for(String fieldName : this.fieldNames) {
			if(pkName.equalsIgnoreCase(fieldName)) {
				names.add(fieldName);
			}else {
				
			}
		}
		return names;
	}
	
	/**
	 * MySQL SQL生成
	 * @return
	 */
	public String getInsertSQL() {
		
		
		StringBuffer sbf = new StringBuffer();
		StringBuffer sbf2= new StringBuffer();
		sbf.append("insert into "+this.name+"(");
		int i = 0;
		for(String fieldName : getNameList()) {
			i++;
			if(i==1) {
				sbf2.append("?");
				sbf.append(fieldName);
			}else {
				sbf.append(", "+fieldName);
				sbf2.append(",?");
			}
		}
		sbf.append(")values(");
		sbf.append(sbf2);
		sbf.append(")");
		return sbf.toString();
	}
	
	
	
	/**
	 * MySQL SQL生成
	 * @return
	 */
	public String getSelectSQL() {
		StringBuffer sbf = new StringBuffer();
		StringBuffer sbf2= new StringBuffer();		
		sbf.append("select * from "+this.name+" where "+this.pk.getName()+"= ?");
		return sbf.toString();
	}
	
	public String getSelectSQLByPKs() {
		StringBuffer sbf = new StringBuffer();
		StringBuffer sbf2= new StringBuffer();		
		sbf.append("select * from "+this.name+" where ");
		boolean interFlag = true;
		for(ColumnInfoBean aColumnInfoBean : pks) {
			if(interFlag) {
				sbf.append(aColumnInfoBean.getName()+"= ? ");
				interFlag = !interFlag;
			}else {
				sbf.append(" and "+aColumnInfoBean.getName()+"= ?");
			}
		}
		return sbf.toString();
	}

	/**
	 * type = mysql
	 *        postgres
	 * @param type
	 * @return
	 */
	public List<String> getScript() {
		String str = "";
		List<String> strs = new ArrayList<String>();
		List<ColumnInfoBean> list = getColumnInfos();
		boolean flag = true;
		StringBuffer sbf = new StringBuffer();
		
		sbf.append("CREATE TABLE "+this.getName()+" (\r\n");
				//CONSTRAINT "ad_mec_pkey" PRIMARY KEY ("ad_mec_id")
		for(ColumnInfoBean infoBean : list) {
			
			if(flag) {
				flag = !flag;
				sbf.append(infoBean.getName()+" "+infoBean.getTypeName());
			}else {
				sbf.append(",\r\n "+infoBean.getName()+" "+infoBean.getTypeName());
			}			
		}
		ColumnInfoBean pkBean = this.getPk();		
		sbf.append(");");
		strs.add(sbf.toString());
		if(pkBean != null) {
			strs.add("alter table "+this.getName()+" add CONSTRAINT "+this.getName()+"_pkey PRIMARY KEY ("+pkBean.getName()+")");
		}
		return strs;
		
	}
	
}
